package com.bd.framework.utils.dbutil;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.io.IOException;
import java.lang.reflect.Method;



/**
 * 数据库操作工具类  对数据库的增删改查
 * @author Administrator
 *
 */


public class DBUtil {
	Properties pro = new Properties();

	
	private Connection getConn() throws SQLException{
		  Connection conn = null;
		  try {
			   pro.load(getClass()
						.getResourceAsStream("/conf/jd.properties"));
			  } catch (IOException e1) {
			   e1.printStackTrace();
			  }
		  String driver = pro.getProperty("jdbc.driver");
		  String url=pro.getProperty("jdbc.url");
		 try{
		  Class.forName(driver).newInstance();  
		  conn = DriverManager.getConnection(url);
		  }
		 catch(Exception e){
			 e.printStackTrace();
		 }
		 return conn;
	}
	/**
     * 
     * 插入一条记录
     * @param sql SQL语句 保证格式如下：1、select * from Table where id=:id 
     *                           2、如果有模糊查询:like :id之间保证一个空格 
     * @param map 参数集 和sql语句的参数保持一致
     * @return 数据库中受影响的记录数
     */
        public int save(String sql, Map<String, Object> map) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Object> list = new ArrayList<Object>();
        try {
            conn = this.getConn();
            pstmt = conn.prepareStatement(this.replaceSql(sql));
            // 替换SQL中的参数
            sql = this.setParameterValue(sql, pstmt, map);
            // 执行操作
           return pstmt.executeUpdate();

        } catch (SQLException e) {
        	e.printStackTrace();
        } finally {
            //此处没写 *********释放连接 Connection and PreparedStatement
        }
		return 0;
    }
 
    /**
     * 更新一条记录
     * @param sql SQL语句 保证格式如下：1、select * from Table where id=:id 
     *                           2、如果有模糊查询:like :id之间保证一个空格 
     * @param map 参数集 和sql语句的参数保持一致
     * @return 数据库中受影响的记录数
     */
    public int update(String sql, Map<String, Object> map) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConn();
            pstmt = conn.prepareStatement(this.replaceSql(sql));
            // 替换SQL中的参数
            this.setParameterValue(sql, pstmt, map);
            // 执行操作
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //此处没写 *********释放连接 Connection and PreparedStatement
        }
		return 0;
    }
 

 
    /**
     *
     * 
     * 
     * @param clazz 实体类型
     * @param sql SQL语句 保证格式如下：1、select * from Table where id=:id 
     *                           2、如果有模糊查询:like :id之间保证一个空格 
     * @param map 参数集 和sql语句的参数保持一致
     * @return 返回一个list 其实是一个实体的list
     */
    public List query(Class clazz, String sql, Map<String, Object> map) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        List list=null;
        list=new ArrayList();
        try {
            conn = this.getConn();
            list=new ArrayList();
            pstmt = conn.prepareStatement(this.replaceSql(sql));
            if(this.parameterCount(sql)>0)
            this.setParameterValue(sql, pstmt, map);
            rs = pstmt.executeQuery();

            Object obj = null;

            while(rs.next()) {

                obj = clazz.newInstance();
                rsmd = rs.getMetaData();
                int count = rsmd.getColumnCount()+1;
                String[] colName = new String[count];
                for (int i = 1; i < count; i++){
                    colName[i] = rsmd.getColumnLabel(i);
                 //   System.out.print(colName[i]+" ");
                    }
                Method[] ms = clazz.getMethods();
                for (Method m : ms)
                    for (int i = 0; i < colName.length; i++)
                        if (m.getName().equalsIgnoreCase("set" + colName[i])) 
                            m.invoke(obj, rs.getObject(colName[i]));
              //    System.out.println(rs.getString(1));
                list.add(obj);
               // System.out.println();
            }
            
        } catch (Exception e) {e.printStackTrace();
        } finally {
            //此处没写 *********释放连接 Connection and PreparedStatement
        }
        return list;
    }
 
    /**
     * 删除
     * @param sql SQL语句 保证格式如下：1、select * from Table where id=:id 
     *                           2、如果有模糊查询:like :id之间保证一个空格 
     * @param map 参数集 和sql语句的参数保持一致
     * @return 数据库中受影响的记录数
     */
    public int detele(String sql, Map<String, Object> map) {
        return this.update(sql, map);
    }
 

     


 
 
    /**
     * 将Map<String, Object>中的参数添加到SQL语句中
     * 
     * @param sql
     *            SQL语句
     * @param pstmt
     *            需要执行的 <code>PreparedStatement</code>
     * @param map
     *            参数集合
     * @throws SQLException
     *             当发生数据库操作异常时
     */
    private String setParameterValue(String sql, PreparedStatement pstmt,
            Map<String, Object> map) throws SQLException {
        int count = this.parameterCount(sql);
        for (int i = 1; i <= count; i++) {
            String pName = this.parameterName(i, sql);
            boolean like = sql.substring(sql.indexOf(":" + pName) - 5,
                    sql.indexOf(":" + pName)).equals("like ");
            sql = sql.replace((":" + pName), ":?");
            try {
                Object o = map.get(pName);
                if (o != null)
                    if (like)
                        pstmt.setObject(i, "%" + map.get(pName) + "%");
                    else
                        pstmt.setObject(i, map.get(pName));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        sql = sql.replaceAll(":", "");
        System.out.println(sql);
        return sql;
    }
 
    /**
     * 替换SQL中的参数为一个正确的SQL。
     * <p>
     * 不改变原有SQL，返回一个新的替换后的SQL。
     * 
     * @param sql
     *            需要替换的SQL
     * @return 替换后的SQL
     */
    private String replaceSql(String sql) {
        String temp = sql;
        int count = this.parameterCount(temp);
        for (int i = 1; i <= count; i++) {
            String pName = this.parameterName(i, temp);
            temp = temp.replace((":" + pName), ":?");
        }
        temp = temp.replaceAll(":", "");
        return temp;
    }

 
    /**
     * 返回SQL语句中参数的个数
     * 
     * @param sql
     * @return
     */
    private int parameterCount(String sql) {
        int count = 0;
        while (sql.indexOf(":") > 0) {
            sql = sql.replaceFirst(":", "");
            count++;
        }
        return count;
    }

 
    /**
     * 返回指定位置的参数名称
     * 
     * @param index
     *            指定第几个参数
     * @param sql
     *            需要的SQL语句
     * @return 指定位置的参数名称
     */
    private String parameterName(int index, String sql) {
        int count = 0;
        if (index == 1)
            count = sql.indexOf(":");
        else {
            for (int i = 1; i < index; i++) {
                sql = sql.replaceFirst(":", "");
                count = sql.indexOf(":");
            }
        }
 
        String s = sql.substring(count + 1, sql.length());
        char[] sqls = s.toCharArray();
        StringBuffer temp = new StringBuffer();
        Pattern p = Pattern.compile("[a-zA-Z0-9_]{1}");
 
        for (int i = 0; i < sqls.length; i++) {
            String c = new String(sqls, i, 1);
            Matcher m = p.matcher(c);
            if (m.matches())
                temp.append(c);
            else
                break;
        }
        return temp.toString();
    }

    public String parameterName1(int index, String sql) {
        int count = 0;
        if (index == 1)
            count = sql.indexOf(":");
        else {
            for (int i = 1; i < index; i++) {
                sql = sql.replaceFirst(":", "");
                count = sql.indexOf(":");
            }
        }
 
        String s = sql.substring(count + 1, sql.length());
        char[] sqls = s.toCharArray();
        StringBuffer temp = new StringBuffer();
        Pattern p = Pattern.compile("[a-zA-Z0-9_]{1}");
 
        for (int i = 0; i < sqls.length; i++) {
            String c = new String(sqls, i, 1);
            Matcher m = p.matcher(c);
            if (m.matches())
                temp.append(c);
            else
                break;
        }
        return temp.toString();
    }
    
    public ResultSet executeQuery(String sql, String[] values) throws Exception {
    	Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        conn = this.getConn();
        pstmt = conn.prepareStatement(sql);
		
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				pstmt.setString(i + 1, values[i]);
			}
		}
		rs = pstmt.executeQuery();
		return rs;
	}

	public int executeUpdate(String sql, String[] values) throws Exception {
		Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        conn = this.getConn();
        pstmt = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				pstmt.setString(i + 1, values[i]);
			}
		}
		return pstmt.executeUpdate();
	}
	
	public int executeUpdates(String sql, String values) throws Exception {
		Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        conn = this.getConn();
        pstmt = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i <100; i++) {
				pstmt.setString(i+1, values);
			}
		}
		return pstmt.executeUpdate();
	}
	
	
	public ResultSet executeQuerys(String sql, String values) throws Exception {
		Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        conn = this.getConn();
        pstmt = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i <100; i++) {
				pstmt.setString(i+1, values);
			}
		}
		rs = pstmt.executeQuery();
		return rs;
	}
	

    public static void main(String[] args) {

    
    	DBUtil dao=new DBUtil();
    	String goodcode="0000007";
    	String[] values={goodcode};
    	String sql="select g.GOODSCODE ,g.GOODSNAME , gp.RETAILPRICE from INFO_GOODS g,INFO_GOODSPRICE  gp where gp.GOODSCODE=g.GOODSCODE and gp.GOODSCODE=:goodsCode ";
    	try {
    		ResultSet rs=dao.executeQuery(sql, values);
			while(rs.next()){
				System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	

  	
    }
}
